package com.bj58.ecdata.dashboard.databll;

import java.util.Calendar;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import net.sf.json.JSONArray;

import org.apache.commons.lang.StringUtils;

import com.alibaba.fastjson.JSONObject;
import com.bj58.ecdata.dashboard.cache.ServiceBeanFactory;
import com.bj58.ecdata.dashboard.dbentity.CityAddLossUser;
import com.bj58.ecdata.dashboard.dbentity.CityCashUser;
import com.bj58.ecdata.dashboard.dbentity.CityIncomeNetflow;
import com.bj58.ecdata.dashboard.dbentity.CityRecharge;
import com.bj58.ecdata.dashboard.dbentity.CitySales;
import com.bj58.ecdata.dashboard.dbentity.CityTransferUser;
import com.bj58.ecdata.dashboard.dbentity.CityVIPTree;
import com.bj58.ecdata.dashboard.dbentity.ConsumeBalance;
import com.bj58.ecdata.dashboard.dbentity.HomePageBanance;
import com.bj58.ecdata.dashboard.dbentity.HomePageNetflow;
import com.bj58.ecdata.dashboard.dbentity.Info_change;
import com.bj58.ecdata.dashboard.dbentity.ResumeNew;
import com.bj58.ecdata.dashboard.service.AbstractBaseService;
import com.bj58.ecdata.dashboard.utils.DDateUtils;
import com.bj58.ecdata.dashboard.utils.DateUtil;
import com.bj58.ecdata.dashboard.utils.ReflectUtil;
import com.bj58.ecdata.dashboard.utils.VelocityTools;
import com.google.common.base.Splitter;
import com.google.common.collect.ArrayListMultimap;
import com.google.common.collect.Lists;
import com.google.common.collect.Multimap;

public class HomePageBLL {
	private static AbstractBaseService<HomePageBanance> balanceService = ServiceBeanFactory.get(HomePageBanance.class,null);
	private static AbstractBaseService<HomePageNetflow> cityNetflowService = ServiceBeanFactory.get(HomePageNetflow.class,null);
	private static AbstractBaseService<CityCashUser> cashUserService = ServiceBeanFactory.get(CityCashUser.class,null);
	private static AbstractBaseService<CityIncomeNetflow> cityIncomeNetflowService = ServiceBeanFactory.get(CityIncomeNetflow.class,null);
	
	private static AbstractBaseService<CityAddLossUser> cityAddLossUserService = ServiceBeanFactory.get(CityAddLossUser.class,null);
	private static AbstractBaseService<CityTransferUser> cityTransferUserService = ServiceBeanFactory.get(CityTransferUser.class,null);
	private static AbstractBaseService<CitySales> citySalesService = ServiceBeanFactory.get(CitySales.class,null);
	private static AbstractBaseService<CityRecharge> cityRechargeService = ServiceBeanFactory.get(CityRecharge.class,null);
	
	private static AbstractBaseService<ResumeNew> resumeNewService = ServiceBeanFactory.get(ResumeNew.class,null);
	private static AbstractBaseService<Info_change> infoChangeService = ServiceBeanFactory.get(Info_change.class,null);
	private static AbstractBaseService<ConsumeBalance> consumeBalanceService = ServiceBeanFactory.get(ConsumeBalance.class,null);
	private static AbstractBaseService<CityVIPTree> vipTreeService = ServiceBeanFactory.get(CityVIPTree.class,null);

	public static final Splitter  commaSplitter = Splitter.on(",");
	
    
	/**
	 * 查询某日信息量总体情况并计算日环比 周同比 月同比
	 * @author zhaoxiang 2015-07-21
	 */
	public static List<List<Object>> getGeneral(String date, String cityName ,String businessName) {
		if (date == null || businessName == null) {
			return Collections.emptyList();
		}
		String lastDay = DDateUtils.varDate(date, "yyyy-MM-dd", Calendar.DATE, -1);// 计算日环比
		String lastWeek = DDateUtils.varDate(date, "yyyy-MM-dd", Calendar.DATE, -7);// 计算周同比

		final String PATTEN_GENERAL = "(real_date = '%s' OR real_date='%s' OR real_date='%s') AND city_name='%s' AND business_name='%s' AND cate_name='all' AND platform='all'";
		
		String condition = String.format(PATTEN_GENERAL, date, lastDay, lastWeek, cityName, businessName);
		List<HomePageNetflow> list = cityNetflowService.getListByConditon(null, condition, null);
		TreeMap<String, List<HomePageNetflow>> map = ReflectUtil.groupByField(list,"real_date");
		
		String[] cols = {"pv","uv","vppv","lide_pv","lide_uv","pv_div_uv","vppv_div_uv"};
		String[] dates = {date, lastDay, lastWeek};
		
		List<List<Object>> finalData = Lists.newArrayList(); //最终结果
		HomePageNetflow todayVo = map.get(date).get(0);
		//遍历查询结果，处理数据
		for(String d : dates){
			List<HomePageNetflow> curData = map.get(d);
			
			HomePageNetflow vo=new HomePageNetflow();
			
			if(curData==null)
			{
				vo=todayVo;
			}
			else 
			{
				vo = curData.get(0);
			}
			List<Object> tmpList = Lists.newArrayList();
			for(String c : cols){
				Object val = ReflectUtil.getVal(vo, c);
				if(StringUtils.equals(date, d)){
					tmpList.add(val);
				}else{
					Object todayVal = ReflectUtil.getVal(todayVo, c);
					tmpList.add(VelocityTools.compRatio(todayVal, val));
				}
			}
			//获得一行结果
			finalData.add(tmpList);
		}

		return finalData;
	}
	
	
	
	private static final String userBalance_COLS = "real_date,%s,jingzhun_num,zhiding_num,zhineng_num";
	private static final String userBalance_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' group by real_date";
	public static String getUserBalanceData(String city, String businessName,
			String cate, String userType, String stat_type, String indexType) {
		String cols = String.format(userBalance_COLS, indexType);
		String condition = String.format(userBalance_CON, cate, city, userType , stat_type, businessName);
		
		List<HomePageBanance> list = balanceService.getListByConditon(cols, condition, "real_date");

		List<Object> days = Lists.newArrayList();
		Multimap<String, Object> dataMap= ArrayListMultimap.create();
		for (HomePageBanance vo : list) {
			days.add(ReflectUtil.getVal(vo, "real_date"));
			for (String column : commaSplitter.split(cols)) {
					dataMap.put(column, ReflectUtil.getVal(vo, column));				
				}
		}

		JSONObject obj = new JSONObject();
        obj.put("time", days);
        obj.putAll(dataMap.asMap());
		return obj.toString();
	}

	
	  
    /**
     * echarts点击获取表格展示数据
     */
    public static String getUserBalanceTableJson(String city, String businessName,
			String cate, String userType, String stat_type, String indexType, String dateVal){
    	String CON_PTN = "real_date='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' group by cate_name";
    	String condition = String.format(CON_PTN, dateVal, city, userType , stat_type, businessName);
      
      List<HomePageBanance> list = balanceService.getListByConditon( null, condition, "user_num desc");

      JSONObject ob = new JSONObject();
      ob.put("dataList", list);
       return ob.toString();
    }
    
    
    private static final String income_COLS = "real_date,jzcash,jzcost_user,zdcash,zdcost_user";
    private static final String income_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' group by real_date";
    public static String getIncomeData(String city, String businessName,
    		String cate, String userType, String stat_type) {
    	String cols = income_COLS;
    	String condition = String.format(income_CON, cate, city, userType , stat_type, businessName);
    	
    	List<CityCashUser> list = cashUserService.getListByConditon(cols, condition, "real_date");
    	
    	List<Object> days = Lists.newArrayList();
    	Multimap<String, Object> dataMap= ArrayListMultimap.create();
    	String[] colStrings = {"jzcash","jzcost_user","zdcash","zdcost_user"};
    	for (CityCashUser vo : list) {
    		days.add(ReflectUtil.getVal(vo, "real_date"));
    		for (String column : colStrings) {
    			dataMap.put(column, ReflectUtil.getVal(vo, column));				
    		}
    	}
    	
    	JSONObject obj = new JSONObject();
    	obj.put("time", days);
    	obj.putAll(dataMap.asMap());
    	return obj.toString();
    }
    
    /**
     * echarts点击获取表格展示数据
     */
    public static String getIncomeTableJson(String city, String businessName,
    		String cate, String userType, String stat_type,  String dateVal){
    	String CON_PTN = "real_date='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' group by cate_name";
    	String condition = String.format(CON_PTN, dateVal, city, userType , stat_type, businessName);
    	
    	List<CityCashUser> list = cashUserService.getListByConditon( null, condition, "jzcash desc");
    	
    	JSONObject ob = new JSONObject(); 
    	ob.put("dataList", list);
    	return ob.toString();
    }
    
    
    private static final String incomeNetFlow_COLS = "real_date,platform,%s";
    private static final String incomeNetFlow_CON = "cate_name='%s' AND city_name='%s' AND stat_type='%s' AND business_name='%s' group by real_date,platform";
    public static String getIncomeNetflowData(String city, String businessName, String cate, String proType, String stat_type, String indexType) {
    	String cols = String.format(incomeNetFlow_COLS, indexType);
    	String condition = String.format(incomeNetFlow_CON, cate, city,  stat_type, businessName);
    	
    	List<CityIncomeNetflow> list = cityIncomeNetflowService.getListByConditon(proType,cols, condition, null);
    	
    	Map<String, List<CityIncomeNetflow>> rsMap = ReflectUtil.groupByField(list, "real_date");
    	Multimap<String, Object> dataMap= ArrayListMultimap.create();
    	String[] plats = {"ALL","PC", "M", "APP"};
    	for (String date : rsMap.keySet()) {
    		List<CityIncomeNetflow> curList = rsMap.get(date);
    		for(String p : plats){
	    		CityIncomeNetflow vo = ReflectUtil.getByColVal(CityIncomeNetflow.class, curList, "platform", p);
	    		Object val = ReflectUtil.getVal(vo, indexType);
	    		dataMap.put(p, val == null ? 0 : val);
    		}
    	}
    	
    	JSONObject obj = new JSONObject();
    	obj.put("time", rsMap.keySet());
    	obj.putAll(dataMap.asMap());
    	return obj.toString();
    }
    
    
    
    /**
     * echarts点击获取表格展示数据
     */
    public static String getIncomeNetflowTableJson(String city, String businessName,
    		String cate, String proType, String stat_type, String indexType, String dateVal){
    	String CON_PTN = "real_date='%s' AND cate_name='%s' AND city_name='%s' AND stat_type='%s' AND business_name='%s' group by cate_name,platform";
    	String condition = String.format(CON_PTN, dateVal, cate, city,  stat_type, businessName);
    	
    	List<CityIncomeNetflow> list = cityIncomeNetflowService.getListByConditon(proType, null, condition, "cost desc");
    	
    	net.sf.json.JSONObject ob = new net.sf.json.JSONObject();
    	ob.put("dataList", list);
    	return ob.toString();
    }
    
    private static final String costUser_COLS = "real_date,%sdisp_user,%scost_user";
    private static final String costUser_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' group by real_date";
    public static String getCostUserData(String city, String businessName, String cate, String proType, String userType, String stat_type) {
    	String cols = String.format(costUser_COLS, proType,proType);
    	String condition = String.format(costUser_CON, cate, city, userType, stat_type, businessName);

    	List<CityCashUser> list = cashUserService.getListByConditon(cols, condition, "real_date");
    	
    	List<Object> days = Lists.newArrayList();
    	Multimap<String, Object> dataMap= ArrayListMultimap.create();
    	String[] colStrings = {proType+"disp_user",proType + "cost_user"};
    	for (CityCashUser vo : list) {
    		days.add(ReflectUtil.getVal(vo, "real_date"));
    		for (String column : colStrings) {
    			dataMap.put(column, ReflectUtil.getVal(vo, column));				
    		}
    	}
    	
    	JSONObject obj = new JSONObject();
    	obj.put("time", days);
    	obj.putAll(dataMap.asMap());
    	return obj.toString();
    }
  
       
    private static final String total_COLS = "real_date, %s";
    private static final String total_CON = "cate_name='all' AND city_name='%s' AND business_name='%s' AND platform='all' AND stat_type='%s'  group by real_date";
    public static String getNetFlowTotalData(String city, String businessName, String indexType, String stat_type) {
    	String cols = String.format(total_COLS, indexType);
    	String condition = String.format(total_CON, city, businessName, stat_type);

    	List<HomePageNetflow> list = cityNetflowService.getListByConditon(cols, condition, "real_date");
    	
    	List<Object> days = Lists.newArrayList();
    	List<Object> valueList = Lists.newArrayList();
    	for (HomePageNetflow vo : list) {
    		days.add(ReflectUtil.getVal(vo, "real_date"));
    		valueList.add(ReflectUtil.getVal(vo, indexType));				
    	}
    	
    	JSONObject obj = new JSONObject();
    	obj.put("time", days);
    	obj.put("data", valueList );
    	return obj.toString();
    }
    
    

    private static final String PLATFORM_COLS = "real_date,platform, %s";
    private static final String PLATFORM_CON = "cate_name='all' AND city_name='%s' AND business_name='%s' AND platform!='all' AND stat_type='%s'  group by real_date,platform";
    /**
	 * 获取三端流量数据的data
	 * @author zhaoxiang 2015-12-14
	 */
	public static String getNetFlowPlatformJson(String city, String businessName, String indexType, String stat_type){
		String platforms[] ={"PC","M","APP"};
		String cols = String.format(PLATFORM_COLS, indexType);
    	String condition = String.format(PLATFORM_CON, city, businessName, stat_type);

		List<HomePageNetflow> list = cityNetflowService.getListByConditon(cols, condition, null);
		TreeMap<String, List<HomePageNetflow>> stat_dateMap = new TreeMap<String, List<HomePageNetflow>>(ReflectUtil.groupByField(list,"real_date"));

		Multimap<String, Object> dataMap= ArrayListMultimap.create();
		// vppv/uv比较特殊
		for (String date : stat_dateMap.keySet()) {
			List<HomePageNetflow> curList = stat_dateMap.get(date);
			for (String plat : platforms) {
				HomePageNetflow vo = ReflectUtil.getByColVal(HomePageNetflow.class,curList,"platform",plat);
				Object value = ReflectUtil.getVal(vo, indexType);
				dataMap.put(plat, value == null ? 0 : value);
			}
		}

		JSONObject ob = new JSONObject();
		ob.put("time", stat_dateMap.keySet());
		for(String p:dataMap.keySet()){
			ob.put(p, dataMap.get(p));
		}

		return ob.toString();
	}
	

    private static final String CATE_COLS = "real_date,platform, %s";
    private static final String CATE_CON = "cate_name='%s' AND city_name='%s' AND business_name='%s' AND platform!='all' AND stat_type='%s'  group by real_date,platform";
    /**
	 * 获取重点类别三端流量数据的data
	 * @author zhaoxiang 2015-12-14
	 */
	public static String getNetFlowCityCateJson(String city, String businessName,String cate, String indexType, String stat_type){
		String platforms[] ={"PC","M","APP"};
		String cols = String.format(CATE_COLS, indexType);
    	String condition = String.format(CATE_CON,cate, city, businessName, stat_type);

		List<HomePageNetflow> list = cityNetflowService.getListByConditon(cols, condition, null);
		TreeMap<String, List<HomePageNetflow>> stat_dateMap = new TreeMap<String, List<HomePageNetflow>>(ReflectUtil.groupByField(list,"real_date"));

		Multimap<String, Object> dataMap= ArrayListMultimap.create();
		// vppv/uv比较特殊
		for (String date : stat_dateMap.keySet()) {
			List<HomePageNetflow> curList = stat_dateMap.get(date);
			for (String plat : platforms) {
				HomePageNetflow vo = ReflectUtil.getByColVal(HomePageNetflow.class,curList,"platform",plat);
				Object value = ReflectUtil.getVal(vo, indexType);
				dataMap.put(plat, value == null ? 0 : value);
			}
		}

		JSONObject ob = new JSONObject();
		ob.put("time", stat_dateMap.keySet());
		for(String p:dataMap.keySet()){
			ob.put(p, dataMap.get(p));
		}

		return ob.toString();
	}
	
	/**
	 * 新增流失客户数  柱形图和折线图
	 */
    private static final String addLossUser_COLS = "real_date,user_type,user_num,arpu";
    private static final String addLossUser_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' AND product_type='%s' group by real_date,user_type";
    public static String getAddLossUserData(String city, String businessName, String cate, String proType, String vipType, String stat_type) {
    	
    	String cols = String.format(addLossUser_COLS);
    	String condition = String.format(addLossUser_CON, cate, city, vipType, stat_type, businessName,proType);
    	List<CityAddLossUser> list =cityAddLossUserService.getListByConditon(cols, condition, "real_date");

    	Map<String, List<CityAddLossUser>> rsMap = ReflectUtil.groupByField(list, "real_date");
    	Multimap<String, Object> dataMap1= ArrayListMultimap.create();
    	Multimap<String, Object> dataMap2= ArrayListMultimap.create();
    	String[] colStrings = {"keep","flow","add"};
    	
    	for (String date : rsMap.keySet()) {
    		List<CityAddLossUser> curList = rsMap.get(date);
    		
    		for(String c : colStrings){
    			CityAddLossUser vo = ReflectUtil.getByColVal(CityAddLossUser.class, curList, "user_type", c);
    			
    			if(vo.getUser_num()==null)
       			{
    				dataMap1.put(c, 0);
       			}
       			else dataMap1.put(c, ReflectUtil.getVal(vo,"user_num"));	    
    			
    			if(vo.getArpu()==null)
       			{
    				dataMap2.put(c, 0);
       			}
       			else dataMap2.put(c, ReflectUtil.getVal(vo,"arpu"));	    		
    		}
    	}
    	
    	JSONObject obj = new JSONObject();
    	obj.put("time", rsMap.keySet());
    	obj.put("dataMap1",dataMap1.asMap());
    	obj.put("dataMap2",dataMap2.asMap());
    	return obj.toString();
    }
    
    /**
     * 新增流失客户数  echarts点击获取表格展示数据
     */
    private static final String addLossUserTable_COLS = "real_date,product_type,user_type,user_num,user_num_chain,arpu,arpu_chain";
    private static final String addLossUserTable_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' AND real_date='%s'";
   public static String getAddLossUserTableJson(String city, String businessName, String cate, String vipType, String stat_type,String dateVal) {
    	
	    String cols = String.format(addLossUserTable_COLS);
   		String condition = String.format(addLossUserTable_CON, cate, city, vipType, stat_type, businessName,dateVal);
   		List<CityAddLossUser> list =cityAddLossUserService.getListByConditon(cols, condition, null);
	
    	JSONObject ob = new JSONObject();
    	ob.put("dataList", list);
    	return ob.toString();
    }
    
   /**
	 * 精准置顶状态转换客户数  柱形图
	 */
   private static final String transferUser_COLS = "real_date,user_type,user_num";
   private static final String transferUser_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' AND product_type='%s' group by real_date,user_type";
   public static String getTransferUserData(String city, String businessName, String cate, String proType, String vipType, String stat_type) {
   	
   	String cols = String.format(transferUser_COLS);
   	String condition = String.format(transferUser_CON, cate, city, vipType, stat_type, businessName,proType);
   	List<CityTransferUser> list =cityTransferUserService.getListByConditon(cols, condition, "real_date");

   	Map<String, List<CityTransferUser>> rsMap = ReflectUtil.groupByField(list, "real_date");
   	Multimap<String, Object> dataMap= ArrayListMultimap.create();
   	String[] colStrings = {"to jingzhun","to zhiding","liushi"};

   	for (String date : rsMap.keySet()) {   		
   		List<CityTransferUser> curList = rsMap.get(date);
   		
   		for(String c : colStrings){
   			CityTransferUser vo = ReflectUtil.getByColVal(CityTransferUser.class, curList, "user_type", c);
   			if(vo.getUser_num()==null)
   			{
   				dataMap.put(c, 0);
   			}
   			else dataMap.put(c, ReflectUtil.getVal(vo,"user_num"));	    		
   		}
   	}
   	
   	JSONObject obj = new JSONObject();
   	//obj.put("time", rsMap.keySet());
   	obj.put("time", rsMap.keySet());
   	obj.putAll(dataMap.asMap());
   	return obj.toString();
   }
   
   /**
    * 精准置顶状态转换客户数  echarts点击获取表格展示数据
    */
   private static final String transferTable_COLS = "real_date,product_type,user_type,user_num,user_num_chain";
   private static final String transferTable_CON = "cate_name='%s' AND city_name='%s' AND is_vip=%s AND stat_type='%s' AND business_name='%s' AND real_date='%s'";
   public static String getTransferUserTableJson(String city, String businessName, String cate, String vipType, String stat_type,String dateVal) {
   	
	    String cols = String.format(transferTable_COLS);
  		String condition = String.format(transferTable_CON, cate, city, vipType, stat_type, businessName,dateVal);
  		List<CityTransferUser> list =cityTransferUserService.getListByConditon(cols, condition, null);
	
   	JSONObject ob = new JSONObject();
   	ob.put("dataList", list);
   	return ob.toString();
   }
  
   /**
	 *销售业绩部分  折线图
	 */
   private static final String sales_CON = "city_name='%s' AND team_type='%s' AND stat_type='%s'  AND business_name='%s'";
   public static String getSalesData(String city, String businessName, String teamType,String pfmType, String stat_type) {
	   
	String condition = String.format(sales_CON, city, teamType, stat_type, businessName);
	String colStrings =pfmType+",wlt_count,blc_count,package_blc_count,no_package_blc_count,new_blc_count,renew_blc_count";

   	List<CitySales> list = citySalesService.getListByConditon(null, condition, "real_date");
   	
   	List<Object> days = Lists.newArrayList();
   	Multimap<String, Object> dataMap= ArrayListMultimap.create();
   
   	for (CitySales vo : list) {
   		days.add(ReflectUtil.getVal(vo, "real_date"));
   		for (String column : commaSplitter.split(colStrings)) {
   			dataMap.put(column, ReflectUtil.getVal(vo, column));				
   		}
   	}
   	JSONObject obj = new JSONObject();
   	obj.put("time", days);
   	obj.putAll(dataMap.asMap());
   	return obj.toString();
   }
   
   /**
    * echarts点击获取表格展示数据
    */
   public static String getSalesTableJson(String city, String businessName, String stat_type, String dateVal,String pfmType){
	   
    String CON_PTN = "real_date='%s' AND city_name='%s' AND stat_type='%s' AND business_name='%s' group by team_type";
   	String condition = String.format(CON_PTN, dateVal, city, stat_type, businessName);
   	
   	List<CitySales> list = citySalesService.getListByConditon( null, condition, null);
   	
   	List<String>showlist=Lists.newArrayList();
   	showlist.add("team_type");
   	for (String column : commaSplitter.split(pfmType)) {
			showlist.add(column);
			showlist.add(column+"_chain");
   	}
   	String[] s={"wlt_count","blc_count","package_blc_count","no_package_blc_count","new_blc_count","renew_blc_count"};
	for (String c:s) {
		showlist.add(c);
		showlist.add(c+"_chain");
	}
   	
   	JSONObject ob = new JSONObject(); 
   	ob.put("showlist", showlist);
   	ob.put("dataList", list);
   	return ob.toString();
   }
   
   /**
	 *续费率部分  折线图
	 */
   private static final String recharge_COLS = "real_date,%s,recharge_rate";
   private static final String recharge_CON = "city_name='%s' AND business_name='%s'";
   public static String getRechargeData(String city, String businessName, String indexType) {
	   
	String select = String.format(recharge_COLS, indexType);
	String condition = String.format(recharge_CON, city, businessName);

  	List<CityRecharge> list = cityRechargeService.getListByConditon(select, condition, "real_date");
  	
  	List<Object> days = Lists.newArrayList();
  	Multimap<String, Object> dataMap= ArrayListMultimap.create();
  
  	for (CityRecharge vo : list) {
  		days.add(ReflectUtil.getVal(vo, "real_date"));
  		for (String column : commaSplitter.split(select)) {
  			dataMap.put(column, ReflectUtil.getVal(vo, column));				
  		}
  	}
  	
  	JSONObject obj = new JSONObject();
  	obj.put("time", days);
  	obj.putAll(dataMap.asMap());
  	return obj.toString();
  }
  
  /**
   * echarts点击获取表格展示数据
   */
  public static String getRechargeTableJson(String city, String businessName,String dateVal){
  	String CON_PTN = "real_date='%s' AND city_name='%s' AND business_name='%s'";
  	String condition = String.format(CON_PTN, dateVal, city, businessName);
  	
  	List<CityRecharge> list = cityRechargeService.getListByConditon( null, condition, null);
  	
  	JSONObject ob = new JSONObject(); 
  	ob.put("dataList", list);
  	return ob.toString();
  }
  
  /** @param businessName
   * @param position
   * @param stat_type
   * @return 
   *  新增简历   堆积图
   */
    private static final String newResumw_COLS = "real_date,info_%s_count,user_valid_%s_count";
    private static final String newResumw_CON = " business_name='%s' AND city_name='%s' AND stat_type='%s' AND cate_name='%s'  group by real_date";
    public static String getNewResumeData(String businessName, String stat_type,String  cateName,String  proType,String cityName) { 
  	   
  		String cols = String.format(newResumw_COLS, proType,proType);
  		String condition = String.format(newResumw_CON, businessName,cityName,stat_type,cateName);
  	
  		List<ResumeNew> list = resumeNewService.getListByConditon(cols, condition, "real_date");		
  		//获取日期map
  		Map<String, List<ResumeNew>> rsMap = ReflectUtil.groupByField(list, "real_date");
  		
  		Multimap<String, Object> dataMap= ArrayListMultimap.create();	
  	   	String[] colStrings = {"resume_rs","resume_info"};
  	   	for (String date : rsMap.keySet()) {   		
  	   		List<ResumeNew> curList = rsMap.get(date);  		
  	   		
  	   		for (ResumeNew info_change : curList) {   			
  	   			dataMap.put(colStrings[0], ReflectUtil.getVal(info_change,"info_"+proType+"_count"));
  	   			dataMap.put(colStrings[1], ReflectUtil.getVal(info_change,"user_valid_"+proType+"_count"));	  			
  			} 
  	   	}
  	   	
  	   	JSONObject obj = new JSONObject();
  	   	//obj.put("time", rsMap.keySet());
  	   	obj.put("time", rsMap.keySet());
  	   	obj.putAll(dataMap.asMap());
  	   	return obj.toString();
  	
  }



  /** * * @param businessName
  /** * * @param position
  /** * * @param stat_type
  /** * * @param dateVal
  /** * * @return 
   * @param dateVal2 
   * @param stat_type2 */
  public static String getNewResumeTableJson(String businessName,String cityName,String cateName,String proType,String stat_type,String dateVal) {
  	

  	String CON_PTN = "business_name='%s' AND city_name='%s' AND stat_type='%s' AND real_date='%s' group by cate_name";
  	String condition = String.format(CON_PTN, businessName, cityName, stat_type , dateVal);
  	String orderBy = "";
  	if("add".equals(proType)){
  		orderBy = "info_add_count desc";
  	}else{
  		orderBy = "info_update_count desc";
  	}
  	List<ResumeNew> list = resumeNewService.getListByConditon(null, condition, orderBy);

  	JSONObject ob = new JSONObject(); 
  	ob.put("dataList", list);
  	return ob.toString();
  }



  /** * * @param businessName
  /** * * @param stat_type
   * 信息变化趋势
   */
  /** * * @return */
  private static final String info_COLS = "real_date,user_type_name,user_%s_count,info_%s_count";
  private static final String info_CON = " business_name='%s' AND city_name='%s' AND stat_type='%s' AND cate_name='%s'  group by real_date,user_type_name";
  private static final String info_CON_ershouche = " business_name='%s' AND city_name='%s' AND stat_type='%s' AND cate_name='all'  group by real_date,user_type_name";
  public static String getInfoData(String businessName, String stat_type, String cateName, String proType,String cityName) {

  	String cols = String.format(info_COLS, proType,proType);
  	String condition ="";
  	
  	if(businessName.equals("二手车"))	
  	{
  		condition=String.format(info_CON_ershouche, businessName,cityName,stat_type);
  	}else
  	{
  		condition=String.format(info_CON, businessName,cityName,stat_type,cateName);
  	}  		
  	
  	List<Info_change> list = infoChangeService.getListByConditon(cols, condition, "real_date");
  	
  	//获取日期map
  	Map<String, List<Info_change>> rsMap = ReflectUtil.groupByField(list, "real_date");
  	
  	Multimap<String, Object> dataMap= ArrayListMultimap.create();
     	String[] colStrings = {"vip_rs","vip_info","freesj_rs","freesj_info","all_rs","all_info","person_rs","person_info"};
     	List<Object> legend = Lists.newArrayList();
     	int i = 0;
     	JSONArray legenJson = new JSONArray();
     	for (String date : rsMap.keySet()) {   		
     		List<Info_change> curList = rsMap.get(date);  		
     		
     		for (Info_change info_change : curList) {
     			String temp = (String) ReflectUtil.getVal(info_change,"user_type_name");
     			if(temp.equals("vip")){
     				legend.add("VIP");
     				dataMap.put(colStrings[0], ReflectUtil.getVal(info_change,"user_"+proType+"_count"));
  				dataMap.put(colStrings[1], ReflectUtil.getVal(info_change,"info_"+proType+"_count"));
     			}else if(temp.equals("免费商家")){
     				legend.add("免费商家");
     				dataMap.put(colStrings[2], ReflectUtil.getVal(info_change,"user_"+proType+"_count"));
  				dataMap.put(colStrings[3], ReflectUtil.getVal(info_change,"info_"+proType+"_count"));
     			}else if(temp.equals("all")){
     				legend.add("all");
     				dataMap.put(colStrings[4], ReflectUtil.getVal(info_change,"user_"+proType+"_count"));
  				dataMap.put(colStrings[5], ReflectUtil.getVal(info_change,"info_"+proType+"_count"));
     		}else{
     				legend.add("个人");
     				dataMap.put(colStrings[6], ReflectUtil.getVal(info_change,"user_"+proType+"_count"));
  				dataMap.put(colStrings[7], ReflectUtil.getVal(info_change,"info_"+proType+"_count"));
     			}
  		   
  		} 
     		if(i==0){
     			legenJson.addAll(legend);
     		}
     		i++;
     	}
     	
     	JSONObject obj = new JSONObject();
     	obj.put("time", rsMap.keySet());
     	obj.put("legend", legenJson);
     	obj.putAll(dataMap.asMap());
     	return obj.toString();
  	
  	
  }
  /** * * @param businessName
  /** * * @param stat_type
  /** * * @param dateVal
  /** * * @return 
   * @param dateVal2 
   * @param stat_type2 
   * @param proType */
  public static String getInfoTableJson(String businessName,String cityName,String cateName,String proType,String stat_type,String dateVal) {
  	String CON_PTN = "business_name='%s' AND city_name='%s' AND cate_name='%s' AND stat_type='%s' AND real_date='%s' ";
  	String CON_PTN_ershouche = "business_name='%s' AND city_name='%s' AND cate_name='all' AND stat_type='%s' AND real_date='%s' ";
  	String condition = "";
  	
	if(businessName.equals("二手车"))	
  	{
  		condition=String.format(CON_PTN_ershouche,  businessName, cityName, stat_type , dateVal);
  	}else
  	{
  		condition=String.format(CON_PTN, businessName, cityName,cateName, stat_type , dateVal);
  	}  
  	
  	List<Info_change> list = infoChangeService.getListByConditon(null, condition, "user_type_name");

  	JSONObject ob = new JSONObject(); 
  	ob.put("dataList", list);
  	return ob.toString();
    
    }
  /**
   * 整体余额消耗变化趋势
   */
  private static final String inventory_COLS = "real_date,%s,is_vip";
  private static final String inventory_CON = " business_name='%s' AND city_name='%s' AND stat_type='%s' AND cate_name='%s' group by real_date,is_vip";
  public static String getInventoryBalanceData(String businessName, String stat_type,  String indexType,String cityName,String cateName) {
	  //getInventoryBalanceData("总体", "day", "全部","总消费","北京")
	    String cols = String.format(inventory_COLS, indexType);
	  	String condition = String.format(inventory_CON, businessName,cityName,stat_type,cateName);
	  	
	  	List<ConsumeBalance> list = consumeBalanceService.getListByConditon(cols, condition, "real_date");
	  	
	    //获取日期map
	  	Map<String, List<ConsumeBalance>> rsMap = ReflectUtil.groupByField(list, "real_date");
	  	
	  	Multimap<String, Object> dataMap= ArrayListMultimap.create();	
  	   	String[] colStrings = {"all","is_vip","no_vip"};
  	   	for (String date : rsMap.keySet()) {   		
  	   		List<ConsumeBalance> curList = rsMap.get(date);  		 	   		  		
	  	   	for (ConsumeBalance consumeBalance : curList) {
	 			Integer temp = (Integer) ReflectUtil.getVal(consumeBalance,"is_vip");
	 			if(temp == 2 ){
	 				dataMap.put(colStrings[0], ReflectUtil.getVal(consumeBalance,indexType));
	 			}else if(temp == 1){ 				
	 				dataMap.put(colStrings[1], ReflectUtil.getVal(consumeBalance,indexType));
	 			}else {
	 				dataMap.put(colStrings[2], ReflectUtil.getVal(consumeBalance,indexType));
	 		    }
	  	   	}
  	   	}
  	   	
  	   	JSONObject obj = new JSONObject();
  	   	obj.put("time", rsMap.keySet());
  	   	obj.putAll(dataMap.asMap());
  	   	return obj.toString();
  }
  
  /**
   * echarts点击获取表格展示数据
   */
  public static String getInventoryBalanceTableJson(String businessName, String cityName,  String indexType,String stat_type,String dateVal,String cateName){
	   
	    String CON_PTN = "business_name='%s' AND city_name='%s' AND stat_type='%s' AND real_date='%s' AND cate_name='%s' group by is_vip";
	  	String condition = String.format(CON_PTN, businessName, cityName, stat_type , dateVal,cateName);
	  	
	  	List<ConsumeBalance> list = consumeBalanceService.getListByConditon(null, condition, "is_vip");

	  	JSONObject ob = new JSONObject(); 
	  	ob.put("dataList", list);
	  	return ob.toString();   
  }
  
  /**
   * 分业务线变化趋势
   */
  private static final String business_COLS = "real_date,business_name,%s";
  private static final String business_CON = "city_name='%s' AND stat_type='%s' AND is_vip=%s AND cate_name='all' group by real_date,business_name";
  public static String getInventoryBusinessData(String businessName, String stat_type, int is_vip, String indexType,String cityName) {
	  	String cols = String.format(business_COLS,indexType);
	  	String condition = String.format(business_CON,cityName,stat_type,is_vip);
	  	
	  	List<ConsumeBalance> list = consumeBalanceService.getListByConditon(cols, condition, "real_date");
	  	
	    //获取日期map
	  	Map<String, List<ConsumeBalance>> rsMap = ReflectUtil.groupByField(list, "real_date");
	  	String[] showCols = {"招聘","房产","黄页","二手","二手车","二手创新","二手推广"};
	  	Multimap<String, Object> dataMap= ArrayListMultimap.create();	
  	   	for (String date : rsMap.keySet()) {
  	   		List<ConsumeBalance> curList = rsMap.get(date);
	  	   	for (String temp : showCols) {
		  	   	ConsumeBalance consumeBalance = ReflectUtil.getByColVal(ConsumeBalance.class, curList, "business_name", temp);
		 		dataMap.put(temp, ReflectUtil.getVal(consumeBalance,indexType));
	  	   	}
  	   	}
  	   	
  	   	JSONObject obj = new JSONObject();
  	   	obj.put("time", rsMap.keySet());
  	   	obj.putAll(dataMap.asMap());
  	   	return obj.toString();
  }
  
  /**
   * echarts点击获取表格展示数据
   */
  public static String getInventoryBusinessTableJson(String businessName, String cityName, String cateName,int is_vip, String indexType,String stat_type,String dateVal){
	   
	    String CON_PTN = " city_name='%s'  AND is_vip=%s AND stat_type='%s' AND real_date='%s' group by business_name";
	  	String condition = String.format(CON_PTN, cityName,is_vip, stat_type , dateVal);
	  	
	  	List<ConsumeBalance> list = consumeBalanceService.getListByConditon(null, condition, "business_name asc");

	  	JSONObject ob = new JSONObject(); 
	  	ob.put("dataList", list);
	  	return ob.toString();   
  }
  
  /**
   * 余额消耗率类别分布
   */
  private static final String ConsumeRate_COLS = "real_date,cate_name,adcash_deposit,adcash_deposit_chain,consume_rate,consume_rate_chain,adcash_consume,adcash_consume_chain,consume_circle,consume_circle_chain";
  private static final String ConsumeRate_CON = " business_name='%s' AND city_name='%s' AND is_vip='%s' AND real_date='%s' AND stat_type='month'";
  public static String getConsumeRateData(String cityName, String businessName, String vipType,String beginDate,String indexType) {
 
	    String cols = ConsumeRate_COLS;
	  	String condition = String.format(ConsumeRate_CON, businessName,cityName,vipType,beginDate);  	
	  	List<ConsumeBalance> list = consumeBalanceService.getListByConditon(cols, condition, "real_date");
	  	
	  	List<Object> relist =Lists.newArrayList();
	  	
  	   	for (ConsumeBalance vo : list) {
  	   		
  	   		if(ReflectUtil.getVal(vo,"cate_name").equals("all"))
  	   		{
  	   			continue;		
  	   		}
  	   		
  	   	    List<Object> info =Lists.newArrayList();
  	   		if(indexType.equals("1"))
  	   		{
	  	   		info.add(ReflectUtil.getVal(vo,"adcash_deposit"));
	  	   	    info.add(ReflectUtil.getVal(vo,"consume_rate"));
	  	   	    info.add(ReflectUtil.getVal(vo,"adcash_deposit_chain"));
	  	   	    info.add(ReflectUtil.getVal(vo,"consume_rate_chain"));
  	   		}
  	   		else if(indexType.equals("2"))
  	   		{
	  	   		info.add(ReflectUtil.getVal(vo,"adcash_consume"));
	  	   	    info.add(ReflectUtil.getVal(vo,"consume_circle"));
	  	   	    info.add(ReflectUtil.getVal(vo,"adcash_consume_chain"));
	  	   	    info.add(ReflectUtil.getVal(vo,"consume_circle_chain"));
  	   		}
  	   	    info.add(ReflectUtil.getVal(vo,"cate_name"));

  	   		relist.add(info);
  	   	}
  	   		   	
  	   	
  	    //时间控件上可选的所有月
  	   	List<String> months = Lists.newArrayList();
  	    condition=" business_name='"+businessName+"' AND city_name='"+cityName+"' AND is_vip='"+vipType+"' AND stat_type='month'";
	  	List<ConsumeBalance> monthsList = consumeBalanceService.getListByConditon("DISTINCT real_date", condition,"real_date asc");
	  	
	  	for (ConsumeBalance vo : monthsList) {
	  		months.add(vo.getReal_date());
		}
	  	
  	   	JSONObject obj = new JSONObject();
  	   	obj.put("relist",relist); 	
  	    obj.put("months",months);
  	   	return obj.toString();
  }
  

  
  /**
   * 会员漏斗树图
   */
  private static final String VIPTREE_COLS = "real_date,stat_date,city_name,business_name,CONCAT(vip_num,',',vip_deposit,',',vip_consume,',',vip_balance) as vip_num,"
  		+ "CONCAT(new_vip_num,',',new_vip_deposit,',',new_vip_consume,',',new_vip_balance) as new_vip_num,CONCAT(db_vip_num,',',db_vip_deposit,',',db_vip_consume,',',db_vip_balance) as db_vip_num,"
  		+ "CONCAT(dbkt_vip_num,',',dbkt_vip_deposit,',',dbkt_vip_consume,',',dbkt_vip_balance) as dbkt_vip_num,CONCAT(dbwkt_vip_num,',',dbwkt_vip_deposit,',',dbwkt_vip_balance) as dbwkt_vip_num,"
  		+ "CONCAT(lk_vip_num,',',lk_vip_deposit,',',lk_vip_consume,',',lk_vip_balance) as lk_vip_num,CONCAT(lkcz_vip_num,',',lkcz_vip_deposit,',',lkcz_vip_consume,',',lkcz_vip_balance) as lkcz_vip_num,"
  		+ "CONCAT(lkwcz_vip_num,',',lkwcz_vip_consume,',',lkwcz_vip_balance) as lkwcz_vip_num,CONCAT(keep_vip_num,',',keep_vip_deposit,',',keep_vip_consume,',',keep_vip_balance) as keep_vip_num,"
  		+ "CONCAT(keepcz_vip_num,',',keepcz_vip_deposit,',',keepcz_vip_consume,',',keepcz_vip_balance) as keepcz_vip_num,CONCAT(keepczkt_vip_num,',',keepczkt_vip_deposit,',',keepczkt_vip_consume,',',keepczkt_vip_balance) as keepczkt_vip_num,"
  		+ "CONCAT(keepczwkt_vip_num,',',keepczwkt_vip_deposit,',',keepczwkt_vip_balance) as keepczwkt_vip_num,CONCAT(keepwczyxh_vip_num,',',keepwczyxh_vip_consume,',',keepwczyxh_vip_balance) as keepwczyxh_vip_num,"
  		+ "CONCAT(keepwczyxhxc_vip_num,',',keepwczyxhxc_vip_consume,',',keepwczyxhxc_vip_balance) as keepwczyxhxc_vip_num,CONCAT(keepwczyxhwxc_vip_num,',',keepwczyxhwxc_vip_consume,',',keepwczyxhwxc_vip_balance) as keepwczyxhwxc_vip_num,"
  		+ "CONCAT(keepinactive_vip_num,',',keepinactive_vip_balance) as keepinactive_vip_num";
  private static final String VIPTREE_CON = " business_name='%s' AND city_name='%s' AND real_date='%s'";
  public static String getVIPTreeData(String business_name, String city_name, String real_date) {
 
	    String cols = VIPTREE_COLS;
	  	String condition = String.format(VIPTREE_CON, business_name,city_name,real_date);  	
	  	//最近一个月的记录或者是选择日期的日数
	  	List<CityVIPTree> list = vipTreeService.getListByConditon(cols, condition,null);
	  	
	  	//上个月的数据
	  	String lastMonth = DateUtil.getMonthBefore(real_date, "yyyy-MM", 1);
	  	lastMonth = lastMonth.substring(0, 4)+"-"+lastMonth.substring(4, 6);
	  	condition = String.format(VIPTREE_CON, business_name,city_name,lastMonth);
	  	List<CityVIPTree> listMonth = vipTreeService.getListByConditon(cols, condition,null);
	  	List<CityVIPTree> datalist = Lists.newArrayList();
	  	if(listMonth.size()!=0){
	  		datalist.add(listMonth.get(0));
	  	}
	  	if(list.size()!=0){  		
	  		datalist.add(list.get(0));
	  	}
	  	//需要显示的全部月份
	  	List<CityVIPTree> monthsList = vipTreeService.getListByConditon("DISTINCT real_date", null,"real_date ");
	  	List<String> months = Lists.newArrayList();
	  	for (CityVIPTree cityVIPTree : monthsList) {
	  		months.add(cityVIPTree.getReal_date());
		}
  	   	JSONObject obj = new JSONObject();
  	   	obj.put("datalist",datalist);
  	    obj.put("months",months);
  	   	return obj.toString();
   }
  //返回会员漏斗树图的最后一个月
  private static final String VIPMONTHS_CON = " business_name='%s' AND city_name='%s'";
  public static String  getLastMonth(String business_name,String city_name){	  
	  String condition = String.format(VIPMONTHS_CON, business_name,city_name);
	  List<CityVIPTree> months = vipTreeService.getListByConditon("DISTINCT real_date", condition,"real_date desc limit 1");
//	  System.out.println(months);
	  if(months.size()!=0){
		  return months.get(0).getReal_date();
	  }
	  return "2015-12";
  }
  
}
